package com.wencst.generateJPA.database.impl;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import com.wencst.generateJPA.bean.ColumnBean;
import com.wencst.generateJPA.bean.TableBean;
import com.wencst.generateJPA.config.CoreContext;
import com.wencst.generateJPA.database.IDatabase;
import com.wencst.generateJPA.utils.StringUtils;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class MysqlDB implements IDatabase{

    private Connection con;
    //获取数据库的元数据
  	private DatabaseMetaData db;
  	private List<String> tableNames;
    public MysqlDB(CoreContext coreContext) {
    	try {
    		Properties props =new Properties();
    		props.setProperty("user", coreContext.getUsername());
    		props.setProperty("password", coreContext.getPassword());
    		props.setProperty("remarks", "true"); //设置可以获取remarks信息 
    		props.setProperty("useInformationSchema", "true");//设置可以获取tables remarks信息
    		Class.forName(coreContext.getDriver());
			con = DriverManager.getConnection(
					coreContext.getURL(),
					props);
			db = con.getMetaData();
			tableNames = coreContext.lisAllTableNames();
		} catch (SQLException e) {
			log.error(e.getMessage(),e);
		} catch (ClassNotFoundException e) {
			log.error(e.getMessage(),e);
		}
    }
	@Override
	public List<TableBean> getAllTables() {
		if (tableNames == null || tableNames.size() <= 0) {
			tableNames = getAllTableNames();
		}
		List<TableBean> allTableBean = new ArrayList<>();
		for (String tableName : tableNames) {
			TableBean tb = getTableDetail(tableName);
			allTableBean.add(tb);
		}
		return allTableBean;
	}

	@Override
	public List<String> getAllTableNames() {
		List<String> tableNames = new ArrayList<String>();
		ResultSet rs = null;
        try {
			//是否有指定生成表，有指定则直接用指定表，没有则全表生成
			rs = db.getTables(null, null, null, new String[] { "TABLE" });
            while (rs.next()) {
            	tableNames.add(rs.getString("TABLE_NAME"));
            }
		} catch (SQLException e) {
			log.error(e.getMessage(),e);
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				log.error(e.getMessage(),e);
			}
		}
		return tableNames;
	}

	@Override
	public List<ColumnBean> getAllColumn(String tableName) {
        List<ColumnBean> allCol = new ArrayList<>();
        //与数据库的连接
        PreparedStatement pStemt = null;
        String tableSql = "select * from " + tableName;
        ResultSet rs = null;
        try {
            pStemt = con.prepareStatement(tableSql);
            rs = pStemt.executeQuery("show full columns from " + tableName);
            while (rs.next()) {
            	String type = rs.getString("Type");
                ColumnBean cb = new ColumnBean();
                cb.setColumnName(rs.getString("Field"));
                cb.setColumnJavaName(StringUtils.trans2Hump(rs.getString("Field"),false));
                cb.setComment(rs.getString("Comment"));
                getTypes(cb,type);
                String primary = rs.getString("Key");
                cb.setPrimaryKey(!StringUtils.isBlank(primary));
                allCol.add(cb);
            }
        } catch (SQLException e) {
			log.error(e.getMessage(),e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
        			log.error(e.getMessage(),e);
                }
            }
            if (pStemt != null) {
                try {
                	pStemt.close();
                } catch (SQLException e) {
        			log.error(e.getMessage(),e);
                }
            }
        }
        return allCol;
	}

	private void getTypes(ColumnBean cb, String type) {
		String[] types1 = null;
		String[] types2 = null;
		String[] types3 = null;
		if (StringUtils.isBlank(type)) {
			return;
		}
		types1 = type.split("\\(");
		cb.setColumnType(types1[0]);
		if (types1.length > 1) {
			types2 = types1[1].split(",");
		}
		if (types2.length > 1) {
			cb.setLength(Integer.parseInt(types2[0]));
			types3 = types2[1].split("\\)");
			cb.setDecimal(Integer.parseInt(types3[0]));
		} else if (types2.length == 1){
			types3 = types2[0].split("\\)");
			cb.setLength(Integer.parseInt(types3[0]));
		}
		cb.setJavaType(StringUtils.sqlType2JavaType(types1[0]));
	}
	
	@Override
	public List<ColumnBean> getAllPrimaryKeys(String tableName) {
		List<ColumnBean> primaryKey = new ArrayList<>();
		ResultSet pkRSet = null;
        try {
			pkRSet = db.getPrimaryKeys(null, null, tableName);
            while( pkRSet.next() ) {
            	String columnName = pkRSet.getString(4);
            	ColumnBean cb = new ColumnBean();
            	cb.setColumnName(columnName);
            	primaryKey.add(cb);
            }
		} catch (SQLException e) {
			log.error(e.getMessage(),e);
		} finally {
			try {
				if (pkRSet != null)
					pkRSet.close();
			} catch (SQLException e) {
				log.error(e.getMessage(),e);
			}
		}
		return primaryKey;
	}

	@Override
	public TableBean getTableDetail(String tableName) {
		TableBean tb = new TableBean();
		ResultSet rs = null;
        try {
			//是否有指定生成表，有指定则直接用指定表，没有则全表生成
			rs = db.getTables(null, null, tableName, new String[] { "TABLE" });
            if (rs.next()) {
            	tb.setTableName(rs.getString("TABLE_NAME"));
            	tb.setComment(rs.getString("REMARKS"));
            	tb.setColumns(getAllColumn(tableName));
            	tb.setJavaBeanName(
            			StringUtils.trans2Hump(
            					tb.getTableName(), true));
            }
		} catch (SQLException e) {
			log.error(e.getMessage(),e);
		} finally {
			try {
				if (rs != null)
					rs.close();
			} catch (SQLException e) {
				log.error(e.getMessage(),e);
			}
		}
		return tb;
	}
}
